%macro get_income;

	proc printto new log = "&logdir./get_income.txt";
	run;

		proc sort data = rlib.ind_owners(keep=person_id where=(person_id ~= 2592233504)) 
			nodupkey
			out = tinlist;
			by person_id;
		run;
		data _null_;
			set tinlist;
			file "&tmpdir./tinlist" dlm=',';
			put person_id;
		run;



		/****************************************************
		*													*
		*					Main SQL call					*
		*													*
		****************************************************/
		proc sql;
		[redacted]
			(
				select
					a.person_id,
					b.[redacted] as agi_p,
					b.[redacted] as year,
					b.[redacted] as fs_p,
					b.[redacted] as cp,
					1 as prim
				from #tinlist as a
					inner join [redacted] as b
						on(a.person_id = b.[redacted])
				where b.[redacted] in(2020,2021)
				order by a.person_id, year, cp

		);
			create table sec as
			select * from connection to iq
			(
				select
					a.person_id,
					c.[redacted] as agi_s,
					c.[redacted] as year,
					c.[redacted] as fs_s,
					c.[redacted] as cp,
					0 as prim
				from #tinlist as a
					inner join [redacted] as b
						on(a.person_id = b.[redacted])
					inner join [redacted] as c
						on(b.[redacted] = c.[redacted] and b.[redacted] = c.[redacted])
				where b.[redacted] in(2020,2021)
					and b.[redacted] = 2
				order by a.person_id, year, cp

		);

	disconnect from iq;
	quit;
	
	
	/********************************
	*								*
	*			Clean income		*
	*								*
	********************************/
	data prim;
		set prim;
		by person_id year;
		if last.year;
		drop cp;
	run;
	
	data sec;
		set sec;
		by person_id year;
		if last.year;
		drop cp;
	run;
	
	data f1040;
		merge prim(in=a) sec(in=b);
		by person_id year;
		if b = 1 then do;
			agi = agi_s;
			fs = fs_s;
			prim = 0;
		end;
		else do;
			agi = agi_p;
			fs = fs_s;
			prim = 1;
		end;
		
		if fs = 2 then income = agi/sqrt(2);
		else income = agi;
		
		dneg = income < 0;
		d0 = 0 <= income < 10000;
		d10 = 10000 <= income < 25000;
		d25 = 25000 <= income < 50000;
		d50 = 50000 <= income < 100000;
		d100 = 100000 <= income < 250000;
		d250 = 250000 <= income < 1000000;
		d1000 = income >= 1000000;
		
		keep person_id year income dneg d0 d10 d25 d50 d100 d250 d1000;
	run;
	
	/****************************************************
	*													*
	*				Merge back into owner data			*
	*													*
	****************************************************/
	proc sql noprint;
		create table joined as
		select
			a.firm_id,
			a.own_share,
			b.*
		from rlib.ind_owners as a
			inner join f1040 as b
				on(a.person_id = b.person_id);
	quit;
	
	proc means data = joined nway noprint;
		class firm_id year;
		weight own_share;
		output out = rlib.owner_incm(drop=_type_ _freq_)
			mean(income dneg d0 d10 d25 d50 d100 d250 d1000) = 
				mean_incm dneg d0 d10 d25 d50 d100 d250 d1000
			median(income) = median_incm;
	run;
	
	
	
			

	proc printto;
	run;
	
	
%mend;